Server Side Load authentication

The Server Side Load option is used for 'Microsoft SQL Server' data sources. When applied, the loading of data from the source database to Planning Space/IPS takes place during an automatic batch job that runs on the IPS Server. Otherwise, a 'client side load' process will run where there is a direct connection made between the user's Planning Space client machine and the source database, and data is transferred to the Planning Space/IPS databases via the client machine.

Server Side Load can be used for database load jobs that are run in the Planning Space client, or jobs that are initiated using the 'SimpleImport' API request in the 'PlanningSpaceDataflow' API (see Web API Documentation-Data loading automation).

SQL Server authentication (interactive and API access)

The simplest way to enable a Server Side Load is to configure a SQL named account (with stored username and password) for the source database, with sufficient permissions to perform the configured SQL query for the data load operation to Planning Space. These credentials can then be used by an automated batch job to make a source database connection and to run the SQL query.

However, the use of SQL named accounts may be discouraged or not allowed under company IT policies, due to the burden of secure password management.

Windows authentication (API access only)

It is simple to configure Windows domain authentication for 'client side load' because the Planning Space client runs in a live user context and it can use the SQL Server Windows authentication protocol to establish the database connection. This is not possible in the case of Server Side Load because the batch job on the IPS Server has no access to the Windows user context that existed when the job process was initiated. The job is executed, like all IPS Server processes, under the Windows user context of the IPS Service Account.

A mechanism to use Windows authentication for Server Side Load, but only using API access, is available in version 16.5 Update 17 and later. The mechanism allows the IPS Server Account to connect to the source database using Windows authentication, and multiple high-level security controls are applied to restrict access to the mechanism. Configuration is required at the system level, i.e., at the IPS Administrator level rather than Tenant Administrator level.

Dedicated mapping templates should be created using the steps explained below, and each template needs to be authorized by the IPS Administrator using the application setting 'Allowed Mapping Templates'. Each template has a unique identifier string (in the form '{tenantname}_{hash code}') which must be included in the list that is contained in the 'Allowed Mapping Templates' setting. To ensure that the content of a template cannot be tampered with after a template has been added to the allowed list, the template unique identifier contains a hash code based on the content of the template, thus any changes to the template will cause the identifier to change and the previous identifier will not be valid.

How to use Windows authentication for Server Side Load

1. Create a database loader mapping template using client side load, with Windows authentication (i.e., do not check the Server Side Load check box). The interactive user account must be granted sufficient access to the source database in order to verify all of the database queries.
2. The data source administrator should ensure that IPS Service Account has minimal, read-only access to the data source. Ideally, the same access should be granted for the interactive user doing the configuration, and for the IPS Service Account.
3. When the loader is verified and working interactively using client side load, the user copies the template's unique identifier using Copy Template Identifier in the template management dropdown menu, and provides this information to the IPS Administrator.
4. The IPS Administrator adds the template unique identifier to the Dataflow application setting 'Allowed Mapping Templates' (in IPS Manager). This is a comma-delimited list of identifiers of mapping templates that are allowed to run with Server Side Load and Windows authentication.
5. When the 'SimpleImport' API is invoked to use that template, it will check that the mapping template is using Windows authentication and and that its identifier matches an entry in the allowed list. If so, the load job will be executed as a Server Side Load, running in the Windows user context of the IPS Service Account. Query Parameters defined in the template will be ignored during execution and a warning will be logged to the job log, indicating that query parameters are not allowed in this situation (this is an additional security restriction because query parameters can be used to alter the behavior of database queries).

The 'SimpleImport' API request specifies a batch template: if there is more than batch step, all of the mapping templates used must be separately configured as described here.

Security considerations

The administrator of the source database must configure appropriate access for the IPS Service Account user to the data that needs to be accessed. This should grant minimal read-only access to only the database tables and views required for the load operation to Planning Space. Identical data access must be (temporarily, at least) permitted for the user account that sets up the Dataflow mapping template interactively using the Planning Space client application.

The IPS Administrator and the source database administrator should review and approve each template before it is added to the allowed list.

Planning Space provides security controls (based on workgroups or user accounts) which can be used as additional access control to individual batch templates and mapping templates in the Load from Database tool.